<?php
header("Content-Type:text/html;charset=utf-8"); 
include '../dbConfig/mysqlFun.php';
require_once 'PHPExcel.php';
require_once 'PHPExcel/IOFactory.php';
require_once 'PHPExcel/Reader/Excel5.php';

if(isset($_POST['importExcelModelSubmit'])){
	if(isset($_FILES['importExcelFile'])&&!empty($_FILES['importExcelFile'])){
		$filename = $_FILES['importExcelFile']['name'];
	    $tmp_name = $_FILES['importExcelFile']['tmp_name'];
	    $tablename = str_replace(".xls","",$filename);
	    $msg = uploadFile($filename,$tmp_name,$tablename);
	    echo $msg;
	}
}

//导入Excel文件
function uploadFile($file,$filetempname,$tablename) 
{
    //自己设置的上传文件存放路径
    $filePath = 'upFile/';
    $str = "";

    //注意设置时区
    $time=date("y-m-d-H-i-s");//去当前上传的时间 
    //获取上传文件的扩展名
    $extend=strrchr ($file,'.');
    //上传后的文件名
    $name=$time.$extend;
    $uploadfile=$filePath.$name;//上传后的文件名地址 
    //move_uploaded_file() 函数将上传的文件移动到新位置。若成功，则返回 true，否则返回 false。
    $result=move_uploaded_file($filetempname,$uploadfile);//假如上传到当前目录下
    //echo $result;
    if($result) //如果上传文件成功，就执行导入excel操作
    {
    	$mysqlDB = new mysqlFun();
    	
        $objReader = PHPExcel_IOFactory::createReader('Excel5');//use excel2007 for 2007 format 
        $objPHPExcel = $objReader->load($uploadfile); 
        $sheet = $objPHPExcel->getSheet(0); 
        $highestRow = $sheet->getHighestRow();           //取得总行数 
        $highestColumn = $sheet->getHighestColumn(); //取得总列数代号
        $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);//总列数
        $highestColumnIndex = $highestColumnIndex-1;
        //echo 'highestRow='.$highestRow;
        //echo 'highestColumnIndex='.$highestColumnIndex;
        
        for ($row = 3;$row <= $highestRow;$row++) 
        {
            $sqlhead = "INSERT INTO {$tablename}";
            $sqlField = " (";
            $sqlVALUES = ") VALUES (";
            //注意highestColumnIndex的列数索引从0开始
            for ($col = 0;$col < $highestColumnIndex;$col++)
            {
            	$tableFieldName = explode("(",$sheet->getCellByColumnAndRow($col, 2)->getValue());
        		$sqlField .= "`".$tableFieldName[0]."`";
                $sqlVALUES .= "'".$sheet->getCellByColumnAndRow($col, $row)->getValue()."'";
        		if($col<$highestColumnIndex-1){
        			$sqlField .= ",";
        			$sqlVALUES .= ",";
        		}
            }    
            $sql = $sqlhead.$sqlField.$sqlVALUES.")";
            //die($sql);
            if(!$mysqlDB->getDB()->query($sql))
            {
                return "导入数据有误，导入失败！";
            }
        }
        return "导入数据成功！";
    }
    else
    {
       return "上传excel文件失败！";
    }
}
?>
<br />
<a href="mysqlAllTable.php" alt="">返回</a>













